//
//  DataBaseManager.swift
//  WeiBoAPP
//
//  Created by ios001 on 2021/8/30.
//  Copyright © 2021 zxf. All rights reserved.
//

import UIKit

class DataBaseManager: NSObject {

     // 创建单例
    private static let manager:DataBaseManager = DataBaseManager()
    
    class func shareManager () -> DataBaseManager {
        return manager
    }
    
    // 数据库名称
    private let dbName = "test.db"
    // 数据库地址
    
    lazy var dbURL:URL = {
//        let manager = FileManager.default
//               let urlForDocument = manager.urls(for: .documentDirectory, in:.userDomainMask)
//               let url = urlForDocument[0] as URL
//        return url;
        
        // 根据窜入的数据库名拼接成是数据库路径
        let fileURL  // = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask )
            = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true).appendingPathComponent(dbName)
        print("数据库地址：\(fileURL.absoluteString)");
        return fileURL
    }()
    
    // fmdatabase对象，
    lazy var db:FMDatabase = {
        let dataBase = FMDatabase(url: dbURL)
        return dataBase
    }()
    
    // fmdatabasequeue 对象，（用于多线程事务处理)
    lazy var dbQuee:FMDatabaseQueue? = {
        // 根据路径返回数据库
        let databaseQueue = FMDatabaseQueue(url: dbURL)
        return databaseQueue
    }()
    
    
    // 打开数据库
    func openDB()  {
        
        let db = DataBaseManager.shareManager().db
         if db.open() {
             print("数据库打开成功!")
         } else {
             print("数据库打开失败: \(db.lastErrorMessage())")
         }
         db.close()
    }
    
        // 创建一表
    func createTable() {
        // AUTOINCREMENT
//        let sql2 = "CREATE TABLE IF NOT EXISTS User( \n" +
//               "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
//               "name TEXT, \n" +
//               "age INTEGER \n" +
//           "); \n"
        
        
        let sql = "CREATE TABLE IF NOT EXISTS USER(\n" + "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" + "title TEXT,\n" + "subTitle TEXT, \n" + "date DATE \n" + "); \n"
        
        // 执行 sql
        let db  = DataBaseManager.shareManager().db
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []) {
                print("创建表成功")
            }else{
                print("创建表失败")
            }
        }
        
        db.close()
    }
    
    // 创建一条记录
    func insertModel(model:DBModel) {
        
        let sql = "INSERT INTO USER (title,subTitle,date) VALUES ('\(model.title ?? "")','\(model.subTitle ?? "")','\(model.date)');"
        
        // 执行 sql
        let db  = DataBaseManager.shareManager().db
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []) {
                print("插入成功")
            }else{
                print("插入失败")
            }
        }
        
        db.close()
    }
    
    // 删除一条数据
    func deleteModel(model:DBModel) {
        let sql = "DELETE FROM USER WHERE id = \(model.db_Id);"
        // 执行 sql
        let db  = DataBaseManager.shareManager().db
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []) {
                print("删除成功")
            }else{
                print("删除失败")
            }
        }
        
        db.close()
    }
    
    // 删除所有数据
    func deleteALLModel() {
        let sql = "DELETE FROM USER ;"
        // 执行 sql
        let db  = DataBaseManager.shareManager().db
        if db.open() {
            if db.executeUpdate(sql, withArgumentsIn: []) {
                print("删除成功")
            }else{
                print("删除失败")
            }
        }
        
        db.close()
    }
/*
     if let res = db.executeQuery(sql, withArgumentsIn: []){
             // 遍历输出结果
             while res.next() {
                 let id = res.int(forColumn: "id")
                 let name = res.string(forColumn: "name")!
                 let age = res.int(forColumn: "age")
                 print(id, name, age)
             }
         }else{
             print("查询失败")
         }
     */
    
    // 读取所有信息
    func readAllDataModel() -> ([DBModel]){
        let sql = "SELECT * FROM USER ;"
        // 执行 sql
        let db  = DataBaseManager.shareManager().db
        var dataList = [DBModel]()
        if db.open() {
            if let res = db.executeQuery(sql, withArgumentsIn: []) {
                // 遍历结果
                while res.next() {
                    let id = res.int(forColumn: "id")
                    let title = res.string(forColumn: "title")
                    let subTitle = res.string(forColumn: "subTitle")
                    let date  = res.date(forColumn: "date")
                    
                    let dbModel = DBModel()
                    dbModel.db_Id = id;
                    dbModel.title = title;
                    dbModel.subTitle = subTitle;
                    dbModel.date = date;
                    dataList.append(dbModel)
                }
            }
            
            print("读取成功")
        }else{
            print("读取失败")
        }
        
        db.close()
        return dataList
    }
    
}
